package preparedStatement;

import bean.Customer;
import org.junit.Test;
import util.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;

/**
 * 针对Customers表的查询操作
 **/
public class CustomerForQuery {

    //对customers表的一个简单查询
    @Test
    public void testQuery() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.预编译SQL语句，返回一个PreparedStatement实例
            String sql = "SELECT id, name, email, birth FROM customers WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql);
            //3.填充占位符
            preparedStatement.setInt(1, 1);
            //4.执行，并返回结果集
            resultSet = preparedStatement.executeQuery();
            //5.处理结果集
            if (resultSet.next()) { // 判断结果集的下一条是否有数据，如果有数据返回true，并指针下移，返回false则指针不下移
                //获取当前这条数据的各个字段的值
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);

                //方式一：
                //System.out.println("id = " + id + ",name = " + name + ",email = " + email + ",birth = " + birth);
                //方式二：
                //Object[] data = new Object[]{id, name, email, birth};
                //方式三：将数据封装成一个对象
                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer.toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源的关闭
            JDBCUtils.closeResourceConnection(connection, preparedStatement, resultSet);
        }
    }


    @Test
    public void testQueryForCustomers() {

        String sqlQuery1 = "SELECT id,name,email,birth FROM customers where id = ?";
        String sqlQuery2 = "select name,email,birth from customers where name=?";
        Customer customer = queryForCustomers(sqlQuery1, 3);
        Customer customer2 = queryForCustomers(sqlQuery2, "汪星人");
        System.out.println(customer.toString());
        System.out.println(customer2.toString());

    }
    //对customers表的通用查询
    public Customer queryForCustomers(String sql, Object ...args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            //2.预编译SQL语句，返回一个preparedStatement实例
            preparedStatement = connection.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            //4.执行，并返回结果集
            resultSet = preparedStatement.executeQuery();
            //5.获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //6.通过ResultSetMetaData来获取结果集的列数
            int columnCount = metaData.getColumnCount();
            //7.处理结果集
            if (resultSet.next()) {
                Customer customer = new Customer();//创建空参的customers对象
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) { //根据列数循环
                    Object value = resultSet.getObject(i + 1);//获取字段的值
                    //获取"表"的每个列的列名
                    String columnName = metaData.getColumnName(i + 1);
                    //给customer对象指定的某个属性，赋值为value。(通过反射)
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);//允许访问私有属性
                    field.set(customer, value);
                }
                return customer;//成功赋值
            }
        } catch (SQLException | NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            //8.关闭资源
            JDBCUtils.closeResourceConnection(connection, preparedStatement, resultSet);
        }
        return null;//对象赋值失败
    }

}
